﻿Imports System.Data.SqlClient

Public Class frm_customer

    Private Sub frm_customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim cn As New SqlConnection("server=MYPC; user = sa; password = 123; database = HOTELAPP")
            cn.Open()
            MessageBox.Show("connect")
            LoadData()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
    Private Sub LoadData()
        Dim cn As New SqlConnection("server=.; user = sa; password = 123; database = HOTELAPP")
        cn.Open()
        Dim cmd As New SqlCommand("SELECT * FROM TblCustomers", cn)

        Dim reader = cmd.ExecuteReader()
        Dim dt As New DataTable
        dt.Load(reader)
        reader.Close()
        Me.DataGridView1.DataSource = dt

    End Sub
    Private Sub btn_CustAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_CustAdd.Click
        Try
            Dim frm As New frm_customerEditor()
            If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
                LoadData()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try
    End Sub
    Private Sub showForm(ByVal form As Form)
        form.Show()

    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        LoadData()

    End Sub

    Private Sub btn_CustEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_CustEdit.Click
        If Me.DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub

        End If
        Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows(0)
        Dim frm As New frm_customerEditor()
        frm.txt_CustID.Text = row.Cells("CustomerID").Value
        frm.txt_CustFullName.Text = row.Cells("CustomerFullName").Value
        frm.txt_CustFname.Text = row.Cells("CustomerFirstName").Value
        frm.txt_CustLname.Text = row.Cells("CustomerLastName").Value
        frm.txt_CobSex.Text = row.Cells("CustomerSex").Value
        'frm.DateTimePicker1 = row.Cells("CustomerDob").Value
        frm.txt_CustPhone.Text = row.Cells("CustomerPhone").Value
        frm.txt_CustEmail.Text = row.Cells("CustomerEmail").Value
        frm.txt_CustPassNo.Text = row.Cells("CustomerPassportNo").Value
        frm.txt_CustCity.Text = row.Cells("CustomerCities").Value
        frm.txt_CustCountry.Text = row.Cells("CustomerCountries").Value
        frm.txt_CustPax.Text = row.Cells("CustomerPax").Value
        frm.txt_CustAdult.Text = row.Cells("CustomerAdult").Value
        frm.txt_CustInfant.Text = row.Cells("CustomerInfant").Value
        frm.txt_CustCom.Text = row.Cells("CustomerAdr").Value

        If frm.ShowDialog() = Windows.Forms.DialogResult.OK Then
            LoadData()
        End If

    End Sub

    Private Sub btn_CustDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_CustDelete.Click
        Try
            If Me.DataGridView1.SelectedRows.Count = 0 Then
                Exit Sub
            End If

            If MessageBox.Show("Are you sure want to delet this?", "Delete", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If

            Dim row As DataGridViewRow = Me.DataGridView1.SelectedRows(0)
            Dim id = row.Cells("CustomerID").Value

            Dim cn As New SqlConnection()
            cn.ConnectionString = "server=.; user = sa; password = 123; database = HOTELAPP"
            cn.Open()

            Dim cmddel As New SqlCommand()
            cmddel.Connection = cn
            cmddel.CommandText = "DELETE FROM TblCustomers WHERE CustomerID = " & id
            cmddel.ExecuteNonQuery()
            LoadData()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)

        End Try


    End Sub

    Private Sub txt_search_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_search.TextChanged
        Try
            DataGridView1.Rows.Clear()
            Dim cn As New SqlConnection()
            cn.ConnectionString = "server=.; user = sa; password = 123; database = HOTELAPP"
            cn.Open()

            Dim cmd As New SqlCommand
            cmd.Connection = cn
            cmd.CommandText = "SELECT * FROM TblCustomers WHERE CustomerFullName LIKE N'%" & txt_search.Text & "%' OR CustomerPhone LIKE N'%" & txt_search.Text & "%' OR CustomerEmail LIKE N'%" & txt_search.Text & "%' OR CustomerPassportNo LIKE N'%" & txt_search.Text & "%' "
            LoadData()

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub
End Class